﻿using Cocbook_DTO;
using System;
using System.Collections.Generic;

namespace Cocbook_DAL
{
    public class AuthorDAL
    {
        public bool CreateAuthor(Author a)
        {
            
            DB.SetQuery("INSERT INTO Author VALUES(@name)");
            DB.AddParameters("name", a.Name);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch   (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.CreateAuthor", e.ToString());
                return false;
            }
        }

        public bool DeleteAuthor(int id)
        {
            DB.SetQuery("DELETE FROM Author WHERE AuthorID = @id");
            DB.AddParameters("id", id);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.DeleteAuthor", e.ToString());
                return false;
            }
        }

        public bool UpdateAuthor(Author a)
        {
            DB.SetQuery("UPDATE Author SET Name = @name WHERE AuthorID = @id");
            DB.AddParameters("id", a.AuthorID);
            DB.AddParameters("name", a.Name);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.UpdateAuthor", e.ToString());
                return false;
            }
        }

        public Author GetAuthorByAuthorName(string name)
        {
            try
            {
                DB.SetQuery("SELECT AuthorID, Name FROM Author WHERE LOWER(Name) = @name");
                DB.AddParameters("name",name.ToLower());
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Author a = new Author();
                if (llo.Count > 0)
                {
                    a.AuthorID = Convert.ToInt16(llo[0][0]);
                    a.Name = Convert.ToString(llo[0][1]);
                }
                else
                    "".Substring(2);
                return a;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.GetAuthorByID", e.ToString());
                return null;
            }
        }

        public Author GetAuthorByBookID(int id)
        {
            try
            {
                DB.SetQuery(@"  SELECT ISNULL(a.AuthorID,0),ISNULL(a.Name,'N/A')
                                FROM Author a,Book_Author ba,Book b
                                WHERE a.AuthorID = ba.AuthorID AND ba.BookID = b.BookID and b.BookID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Author a = new Author();
                if (llo.Count > 0)
                {
                    a.AuthorID = Convert.ToInt16(llo[0][0]);
                    a.Name = Convert.ToString(llo[0][1]);
                }
                else
                    "".Substring(2);
                return a;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.GetAuthorByBookID", e.ToString());
                return null;
            }
        }

        public Author GetAuthorByID(int id)
        {
            try
            {
                DB.SetQuery("SELECT AuthorID, Name FROM Author WHERE AuthorID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Author a = new Author();
                if (llo.Count > 0)
                {
                    a.AuthorID = Convert.ToInt16(llo[0][0]);
                    a.Name = Convert.ToString(llo[0][1]);
                }
                else
                    "".Substring(2);
                return a;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.GetAuthorByID", e.ToString());
                return null;
            }
        }

        public List<Author> GetAllAuthor()
        {
            try
            {
                DB.SetQuery("SELECT AuthorID, Name FROM Author");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Author> Author = new List<Author>();
                Author a;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        a = new Author();
                        a.AuthorID = Convert.ToInt16(lo[0]);
                        a.Name = Convert.ToString(lo[1]);
                        Author.Add(a);
                    }
                }
                else
                    "".Substring(2);
                return Author;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.AuthorDAL.GetAllAuthor", e.ToString());
                return null;
            }
        }
    }
}
